"""
Author: Bu Xiangfeng
E-mail: xiangfeng_006@foxmail.com
Date: 2018-10-25
"""
import pymysql
import random

class ReadDataFromSQL(object):
    def __init__(self, db_name='software_qc'):
        self._conn = pymysql.connect(host='111.231.89.200',
                                     port=3306,
                                     user='user01',
                                     passwd='user01',
                                     db=db_name,
                                     charset='utf8'
                                     )

        self._cursor = self._conn.cursor()
        self._cursor = self._conn.cursor(cursor=pymysql.cursors.DictCursor)
        self._user_table_name = None
        self.need_new_table()

    def read_data_random(self, limit_num=10, fail_record=None):
        if not fail_record:
            cmd = "SELECT * FROM  questions  ORDER BY RAND() LIMIT %s" %  limit_num
        else:
            cmd = "select * from questions  where id in (select id from (select * from %s order by fail_count  " \
                  "desc limit %s ) as t)" % (self._user_table_name, limit_num)

        self._cursor.execute(cmd)
        self._conn.commit()
        record = self._cursor.fetchall()
        return record

    def update_count(self, col_name, index):
        self._cursor.execute('update {0} set {1}={1} + 1 where id={2}'.format(self._user_table_name, col_name, index))
        self._conn.commit()

    def conn_exit(self):
        self._cursor.close()
        self._conn.close()

    def insert_data(self, key_list, value_list):
        cmd = "replace into %s (%s) VALUES ('%s')" % (self._user_table_name, ",".join(key_list), "','".join(value_list))
        self._cursor.execute(cmd)
        self._conn.commit()

    def clean_table(self):
        cmd = "delete from %s" % self._user_table_name
        self._cursor.execute(cmd)
        self._conn.commit()

    def create_table(self, table_name):
        cmd = "CREATE TABLE `%s` (`id` int(255) NOT NULL AUTO_INCREMENT, `pass_count` int(255) unsigned" \
              " zerofill DEFAULT '0', `fail_count` int(255) unsigned zerofill DEFAULT '0', PRIMARY KEY (`id`)) " \
              "ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;" % table_name
        self._cursor.execute(cmd)
        self._conn.commit()

    def drop_table(self):
        cmd = "drop table %s" % self._user_table_name
        self._cursor.execute(cmd)
        self._conn.commit()

    def need_new_table(self):
        table_name = input("Your user name:").strip()
        if not table_name:
            table_name = "guest_table"
        else:
            table_name += '_table'
        self._user_table_name = table_name

        try:
            self.create_table(table_name)
            self._user_table_name = table_name
            keys = ["pass_count", 'fail_count']
            values = ['0', '0']
            for i in range(1, 201):
                self.insert_data(key_list=keys, value_list=values)
            print("the table has been created ")
        except:
            pass

mysql_data = ReadDataFromSQL()


class UserInterface:
    def __init__(self):
        self.options = "ABCDEF"

    def start(self, limit_num=10, fail_record_only=None):
        wrong_counter = 0
        title_index = 1
        for question_dic in mysql_data.read_data_random(limit_num=limit_num, fail_record=fail_record_only):
            print("===========\n", "%s.%s" % (title_index, question_dic['question']), sep='\n')
            index = question_dic['id']

            if question_dic['A']:
                answer_info = question_dic.get(question_dic['Answer'].strip())
                user_answer = self.option_question(question_dic)
            else:
                answer_info = question_dic['Answer'].strip()
                user_answer = self.true_false_question()

            if user_answer == answer_info:
                col_name = 'pass_count'
                print("Right!")
            else:
                col_name = 'fail_count'
                wrong_counter +=1
                print("WRONG!!\nRight answer:%s\n" % answer_info)

            mysql_data.update_count(col_name=col_name, index=index)
            title_index += 1

        PassRate = (limit_num - wrong_counter)*100/limit_num
        print("WRONG:%s  RIGHT:%s PassRate:%.2f%%" % (wrong_counter, (limit_num - wrong_counter), PassRate))

    def option_question(self, question_dic):
        questions_list = list()
        for opt in self.options:
            opt_info = question_dic.get(opt)
            if opt_info:
                questions_list.append(question_dic[opt])
            else:
                break
        random.shuffle(questions_list)

        new_order_question = dict(zip(self.options, questions_list))

        for key, value in new_order_question.items():
            print("%s.%s  " % (key, value), end="\n")

        while True:
            selected = input("\nYour answer:").upper()
            user_answer = new_order_question.get(selected)
            if user_answer:
                return user_answer
            else:
                print("Out of range, please input list 'A/B/C/D...'")

    def true_false_question(self):
        while True:
            user_answer = input("\nYour answer:").upper()
            if user_answer in "NY":
                return user_answer
            else:
                print("输入'Y' or 'N' ")


if __name__ == "__main__":
    user = UserInterface()

    while True:
        questions_type = input("随机抽取题目类型(1.随机; 2.错题优先.  默认1):").strip()
        try:
            questions_type = int(questions_type)
            if questions_type == 1:
                fail_record_only = None
            else:
                fail_record_only = True
        except:
            fail_record_only = None

        questions_num = input("随机抽取题目个数（默认10个）:").strip()
        try:
            limit_num = int(questions_num)
        except:
            limit_num = 10

        user.start(limit_num, fail_record_only)
        continue_test = input("再测一次？（Y/N）:").strip().upper()

        if continue_test == 'N':
            break
